package DAO.MySQL;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import Model.ProductsSearchbyCriteria;

import DAO.DAOException;
import DAO.ProductsDAO;
import Model.Product;
import Util.JDBCUtil;

public class ProductsDAOImpl implements ProductsDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/mydb";
	private static final String dbUserName = "root";
	private static final String dbPassword = "root";

	public Product findProduct(int partnumber) throws DAOException {
		String selectSql = "SELECT * FROM Products WHERE partnumber='"
				+ partnumber + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Product P = new Product();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				P.setColor(rs.getString("color"));
				P.setProductname(rs.getString("productname"));
				P.setDescription(rs.getString("description"));
				P.setDimension(rs.getString("dimension"));
				P.setInstock(rs.getInt("instock"));
				P.setManufacturer(rs.getString("manufacturer"));
				P.setMinorder(rs.getInt("minorder"));
				P.setProductnum(rs.getInt("partnumber"));
				P.setReorderlevel(rs.getInt("reorderlevel"));
				P.setShelflocation(rs.getString("shelflocation"));
				P.setUnitprice(rs.getDouble("unitprice"));

			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(P.toString());
		return P;
	}

	public ArrayList<Product> findAllProduct() throws DAOException {
		ArrayList<Product> items = new ArrayList<Product>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT * FROM Products;";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();

			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				Product P = new Product();
				P.setColor(rs.getString("color"));
				P.setDescription(rs.getString("description"));
				P.setProductname(rs.getString("productname"));
				P.setDimension(rs.getString("dimension"));
				P.setInstock(rs.getInt("instock"));
				P.setManufacturer(rs.getString("manufacturer"));
				P.setMinorder(rs.getInt("minorder"));
				P.setProductnum(rs.getInt("partnumber"));
				P.setReorderlevel(rs.getInt("reorderlevel"));
				P.setShelflocation(rs.getString("shelflocation"));
				P.setUnitprice(rs.getDouble("unitprice"));

				items.add(P);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	public void insertProduct(Product P) throws DAOException {
		String insertSql = "INSERT INTO Products(partnumber,unitprice,description,color,dimension,manufacturer,reorderlevel,minorder,shelflocation,instock,productname) Values('" + P.getProductnum()
				+ "', '" + P.getUnitprice() + "', '" + P.getDescription()
				+ "', '" + P.getColor() + "', '" + P.getDimension() + "', '"
				+ P.getManufacturer() + "', '" + P.getReorderlevel() + "', '"
				+ P.getMinorder() + "', '" + P.getShelflocation() + "', '"
				+ P.getInstock() + "', '" + P.getProductname() + "');";

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			st.executeUpdate(insertSql);
			st.close();
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			try {
				// conn.rollback();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void updateProduct(Product P) throws DAOException {
		String updateSql = "UPDATE products SET unitprice='" + P.getUnitprice()
				+ "',description= '" + P.getDescription() + "',color='"
				+ P.getColor() + "',dimension='" + P.getDimension()
				+ "',manufacturer='" + P.getManufacturer()
				+ "', reorderlevel='" + P.getReorderlevel() + "', minorder='"
				+ P.getMinorder() + "',shelflocation='" + P.getShelflocation()
				+ "',instock='" + P.getInstock()

				+ "' WHERE partnumber='" + P.getProductnum() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

	}

	public void deleteProduct(Product P) throws DAOException {
		String deleteSql = "DELETE FROM Products WHERE partnumber='"
				+ P.getProductnum() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();

			st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	public void updateInstock(int partnum, int quantity) {
		Connection con = JDBCUtil.GetConnection();
		try {
			Product p = this.findProduct(partnum);
			int currentStock = p.getInstock() - quantity;

			String sql = "update products set instock=? where partnumber=? ";
			PreparedStatement ps;
			try {
				ps = con.prepareStatement(sql);
				ps.setInt(1, currentStock);
				ps.setInt(2, partnum);
				ps.execute();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} catch (DAOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public ArrayList<Product> findProductsByCriteria(Product psc)
			throws DAOException {
		ArrayList<Product> list = new ArrayList<Product>();
		String selectSql = null;
		if (psc.getDescription().trim().equalsIgnoreCase("")) {

			selectSql = "SELECT * FROM products WHERE partnumber LIKE '"
					+ psc.getProductnum() + "%';";
		} else if (psc.getProductnum() == 0) {

			selectSql = "SELECT * FROM products WHERE description LIKE '"
					+ psc.getDescription().trim() + "%';";
		} else {
			selectSql = "SELECT * FROM products WHERE partnumber LIKE '"
					+ psc.getProductnum() + "%' AND description LIKE '"
					+ psc.getDescription() + "%';";
		}

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				Product P = new Product();

				P.setProductnum(rs.getInt("partnumber"));
				P.setUnitprice(rs.getDouble("unitprice"));
				P.setDescription(rs.getString("description"));
				P.setInstock(rs.getInt("instock"));
				System.out.println(P.toString());
				list.add(P);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}

		return list;

	}

	public ArrayList<Product> findProductlessStock() throws DAOException {
		ArrayList<Product> list = new ArrayList<Product>();
		String selectSql = "SELECT * FROM Products WHERE instock< reorderlevel";

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Product P = null;
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(selectSql);			
			while (rs.next()) {
				P=new Product();
				P.setProductnum(rs.getInt("partnumber"));
				P.setColor(rs.getString("color"));
				P.setDescription(rs.getString("description"));
				P.setDimension(rs.getString("dimension"));
				P.setInstock(rs.getInt("instock"));
				P.setManufacturer(rs.getString("manufacturer"));
				P.setMinorder(rs.getInt("minorder"));
				P.setReorderlevel(rs.getInt("reorderlevel"));
				P.setShelflocation(rs.getString("shelflocation"));
				P.setUnitprice(rs.getDouble("unitprice"));
				P.setProductname(rs.getString("productname"));
				list.add(P);
			}
			st.close();
		} catch (Exception e) {
			
			
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(P.toString());
		return list;

	}

	

}
